package merlin.dao;

import java.util.*;
import java.sql.*;

import merlin.beans.*;

public class DirectorDetailsDAO implements java.io.Serializable{

	Connection con;

	public DirectorDetailsDAO(Connection con){
		this.con = con;
	}

	public DirectorDetailsBean selectDirectorDetailsByPK(String pk)throws SQLException{
		PreparedStatement pstmt = con.prepareStatement("SELECT COMPANY_NUMBER,DIRECTOR,DIRECTOR_SORT,DIRECTOR_FIRST_NAME,DIRECTOR_SURNAME,DIRECTOR_DATE_OF_BIRTH FROM DIRECTOR_DETAILS WHERE COMPANY_NUMBER = ? ");
		pstmt.setString(1,pk);
		ResultSet rs = pstmt.executeQuery();
		if(rs.next()){
			DirectorDetailsBean bn = new DirectorDetailsBean();
			bn.setCompanyNumber(rs.getString("COMPANY_NUMBER"));
			bn.setDirector(rs.getString("DIRECTOR"));
			bn.setDirectorSort(rs.getInt("DIRECTOR_SORT"));
			bn.setFirstname(rs.getString("DIRECTOR_FIRST_NAME"));
			bn.setSurname(rs.getString("DIRECTOR_SURNAME"));
			bn.setDateOfBirth(rs.getString("DIRECTOR_DATE_OF_BIRTH"));
			return bn;
		}else{
			return null;
		}
	}

	public DbResultSet selectDirectorDetailsRecordsByCompanyNumber(String companyNumber)throws SQLException{

		ArrayList records = new ArrayList();
		PreparedStatement pstmt = con.prepareStatement("SELECT DIRECTOR_FIRST_NAME, DIRECTOR_SURNAME, DIRECTOR_DATE_OF_BIRTH, DD.DIRECTOR FROM NSD.DIRECTOR_DETAILS DD WHERE COMPANY_NUMBER = ?");
		pstmt.setString(1,companyNumber);
		ResultSet rs =  pstmt.executeQuery();
		while(rs.next()){
			DirectorDetailsBean bn = new DirectorDetailsBean();
			bn.setDirector(rs.getString("DIRECTOR"));
			bn.setFirstname(rs.getString("DIRECTOR_FIRST_NAME"));
			bn.setSurname(rs.getString("DIRECTOR_SURNAME"));
			bn.setDateOfBirth(rs.getString("DIRECTOR_DATE_OF_BIRTH"));
			records.add(bn);
		}
		rs.close();
		pstmt.close();
		if(!records.isEmpty())return new DbResultSet(records,records.size()); else return new DbResultSet();
	}

	public DirectorDetailsBean getFWBSDirectorByCompanyNumber(String registeredNumber, boolean selectYoungest)throws SQLException{

		DirectorDetailsBean bean = null;

		String sql = "SELECT TOP(0,1) " +
		"FBWS_DIRECTORS_COMPANY_NAME, " +
		"TRIM(COALESCE(FBWS_DIRECTORS_DIRECTOR_PREFIX,'Mr')), " +
		"FBWS_DIRECTORS_DIRECTOR_FIRST_NAME, " +
		"FBWS_DIRECTORS_DIRECTOR_SURNAME, " +
		"FBWS_DIRECTORS_DIRECTOR_POSITION, " +
		"CAST(SUBSTRING(FBWS_DIRECTORS_DIRECTOR_DATE_OF_BIRTH FROM CHAR_LENGTH(FBWS_DIRECTORS_DIRECTOR_DATE_OF_BIRTH) - 3 FOR 4) AS INT) as DOB, " +
		"CASE TRIM(COALESCE(FBWS_DIRECTORS_DIRECTOR_PREFIX,'Mr')) " +
		"	WHEN 'Mr' THEN 1 " +
		"	WHEN 'Mister' THEN 1 " +
		"	WHEN 'Dr' THEN 2 " +
		"	WHEN 'Ms' THEN 3 " +
		"	WHEN 'Miss' THEN 3 " +
		"	WHEN 'Mister' THEN 3 " +
		"	ELSE 4 " +
		"END AS PREFIX_SORT, " +
		"CASE FBWS_DIRECTORS_DIRECTOR_POSITION  " +
		"	WHEN 'Director' THEN 1  " +
		"	WHEN 'Company Secretary' THEN 3  " +
		"	ELSE 2  " +
		"END AS COMP_POSITION  " +
		"FROM FBWS_DIRECTORS  " +
		"WHERE FBWS_DIRECTORS_REGISTERED_NUMBER = ? AND FBWS_DIRECTORS_DIRECTOR_DATE_OF_BIRTH IS NOT NULL AND CHAR_LENGTH(TRIM(FBWS_DIRECTORS_DIRECTOR_DATE_OF_BIRTH)) = 10 " +
		"ORDER BY PREFIX_SORT ASC, COMP_POSITION ASC, DOB " + (selectYoungest ? "DESC" : "ASC");


		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1,registeredNumber);

		ResultSet rs = pstmt.executeQuery();

		if(rs.next()){
			bean = new DirectorDetailsBean();
			bean.setCompanyName(rs.getString(1));
			bean.setPrefix(rs.getString(2));
			bean.setFirstname(rs.getString(3));
			bean.setSurname(rs.getString(4));
			bean.setPosition(rs.getString(5));
			bean.setYearOfBirth(rs.getInt(6));
		}

		rs.close();
		pstmt.close();

		return bean;
	}

	@SuppressWarnings("unchecked")
	public Map getDirectorSections()throws SQLException{

		Map directorSections  = new LinkedHashMap();

		String sql = "SELECT DS_ID, DS_TEXT FROM DIRECTOR_SECTIONS ORDER BY DS_ID";
		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery(sql);

		while(rs.next()){
			directorSections.put(rs.getObject(1), rs.getObject(2));
		}
		rs.close();
		stmt.close();
		return directorSections;
	}

	public DirectorDetailsBean getDirectorByCompanyNumberForBIBService(String registeredNumber, String whereClause, String orderByClause)throws SQLException{

		DirectorDetailsBean bean = null;

		String sql = "SELECT TOP(0,1) " +
			"DIRECTOR_TITLE," +	
			"DIRECTOR_FIRST_NAME," +	
			"DIRECTOR_SURNAME," +	
			"DIRECTOR_POSITION," +
			"DIRECTOR_DATE_OF_BIRTH," +
			"DIRECTOR_ID," + 
			"DIRECTOR_SERVICE_ADDRESS_ID," +	
			"DIRECTOR_LEVEL1," +
			"DIRECTOR_LEVEL2," +
			"DIRECTOR_SHAREHOLDER1," +
			"DIRECTOR_SHAREHOLDER2 " + 	
			"FROM DIRECTOR_DETAILS  " +
			"WHERE COMPANY_NUMBER = ? ";
		if(whereClause != null && whereClause.length() > 0){
			sql += " AND " + whereClause; 
		}
		if(orderByClause != null && orderByClause.length() > 0){
			sql += " ORDER BY " + orderByClause;
		}
		//System.out.println(registeredNumber + " : " + sql);

		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1,registeredNumber);

		ResultSet rs = pstmt.executeQuery();

		if(rs.next()){
			bean = new DirectorDetailsBean();
			bean.setCompanyNumber(registeredNumber);
			bean.setPrefix(rs.getString(1));
			bean.setFirstname(rs.getString(2));
			bean.setSurname(rs.getString(3));
			bean.setPosition(rs.getString(4));
			bean.setDateOfBirth(rs.getString(5));
			bean.setDirectorId(rs.getInt(6));
			bean.setServiceAddressId(rs.getInt(7));
			bean.setLevel1(rs.getInt(8));
			bean.setLevel2(rs.getInt(9));
			bean.setShareholder1(rs.getInt(10));
			bean.setShareholder2(rs.getInt(11));
		}

		rs.close();
		pstmt.close();

		return bean;
	}

	public DirectorDetailsBean getHomeAddress(DirectorDetailsBean bean)throws SQLException{
		String sql = "SELECT DSA_ADDRESS1, DSA_ADDRESS2, DSA_ADDRESS3, DSA_ADDRESS4, DSA_POSTCODE " +
				"FROM DIRECTOR_SERVICE_ADDRESS " +
				"WHERE DSA_ADDID = ?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1, bean.getServiceAddressId());
		
		ResultSet rs = pstmt.executeQuery();
		if(rs.next()){
			bean.setAddress1(rs.getString(1));
			bean.setAddress2(rs.getString(2));
			bean.setAddress3(rs.getString(3));
			bean.setAddress4(rs.getString(4));
			bean.setPostcode(rs.getString(5));
		}
		return bean;
	}

	public DirectorDetailsBean getTradingAddress(DirectorDetailsBean bean)throws SQLException{
		String sql = "SELECT TA_ADDRESS1, TA_ADDRESS2, TA_ADDRESS3, TA_ADDRESS4, TA_POSTCODE, TA_TELEPHONE " +
				"FROM TRADING_ADDRESS " +
				"WHERE TA_ORG = ? AND TA_MTA_INDICATOR='Y'";
		System.out.println(bean.getCompanyNumber() + " : " + sql);
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1, bean.getCompanyNumber());
		
		ResultSet rs = pstmt.executeQuery();
		if(rs.next()){
			bean.setAddress1(rs.getString(1));
			bean.setAddress2(rs.getString(2));
			bean.setAddress3(rs.getString(3));
			bean.setAddress4(rs.getString(4));
			bean.setPostcode(rs.getString(5));
			bean.setTelephone(rs.getString(6));
		}
		return bean;
	}
	
	public DirectorDetailsBean getRegisteredOfficeAddress(DirectorDetailsBean bean)throws SQLException{
		String sql = "SELECT ADDRESS_LINE1, ADDRESS_LINE2, TOWN, COUNTY, POSTCODE, TELEPHONE " +
				"FROM LOOKUP_DETAILS " +
				"WHERE COMPANY_NUMBER = ?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1, bean.getCompanyNumber());
		
		ResultSet rs = pstmt.executeQuery();
		if(rs.next()){
			bean.setAddress1(rs.getString(1));
			bean.setAddress2(rs.getString(2));
			bean.setAddress3(rs.getString(3));
			bean.setAddress4(rs.getString(4));
			bean.setPostcode(rs.getString(5));
			bean.setTelephone(rs.getString(6));
		}
		return bean;
	}
}